﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using ECOSYSTEM_Dominio;

namespace ECOSYSTEM_DAL
{
    public class TransportistaDAL
    {
        SqlConnection cnn;
        public Boolean guardarTransportista(Transportista elTransportista) { 
            try
            {
                cnn = (SqlConnection)ConexionSQL.getInstancia().getConexion();
                cnn.Open();
                SqlCommand cmd = new SqlCommand("RegistrarTransportista",cnn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@TransportistaID", SqlDbType.Int);
                cmd.Parameters["@TransportistaID"].Direction = ParameterDirection.InputOutput;
                cmd.Parameters.Add("@RUCTrans", SqlDbType.NVarChar,11);
                cmd.Parameters.Add("@RazonSocial", SqlDbType.NVarChar,100);
                cmd.Parameters.Add("@Direccion", SqlDbType.NVarChar,100);
                cmd.Parameters.Add("@Telefono", SqlDbType.NVarChar,15);
                cmd.Parameters.Add("@Email", SqlDbType.NVarChar,100);
                cmd.Parameters.Add("@FecReg", SqlDbType.NVarChar,12);
                cmd.Parameters["@TransportistaID"].Value = elTransportista.TransportistaID;
                cmd.Parameters["@RUCtrans"].Value = elTransportista.RUCTransp;
                cmd.Parameters["@RazonSocial"].Value = elTransportista.RazonSoacialTransp;
                cmd.Parameters["@Direccion"].Value = elTransportista.DirecTransp;
                cmd.Parameters["@Telefono"].Value = elTransportista.TelefTransp;
                cmd.Parameters["@Email"].Value = elTransportista.EmailTransp;
                cmd.Parameters["@FecReg"].Value = elTransportista.FecRegTransp;
                cmd.ExecuteNonQuery();
                elTransportista.TransportistaID =(Int32)(cmd.Parameters["@TransportistaID"].Value);
            }
            catch (SqlException ex) { throw new Exception(ex.Message); }
            catch (Exception e) { throw new Exception(e.Message); }
            finally
            {
                if (cnn.State == ConnectionState.Open)
                {
                    cnn.Close();
                }
            }
            return true;
        }
        public Boolean ActualizarTransportista(Transportista elTransportista, String Ruc)
        {
            try
            {
                cnn = (SqlConnection)ConexionSQL.getInstancia().getConexion();
                cnn.Open();
                SqlCommand cmd = new SqlCommand("ActualizarTransportista", cnn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@TransportistaID", SqlDbType.Int);
                cmd.Parameters.Add("@RUCTrans", SqlDbType.NVarChar, 11);
                cmd.Parameters.Add("@RazonSocial", SqlDbType.NVarChar, 100);
                cmd.Parameters.Add("@Direccion", SqlDbType.NVarChar, 100);
                cmd.Parameters.Add("@Telefono", SqlDbType.NVarChar, 15);
                cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 100);
                cmd.Parameters.Add("@RucNuevo", SqlDbType.NVarChar,11);
                cmd.Parameters["@TransportistaID"].Value = elTransportista.TransportistaID;
                cmd.Parameters["@RUCtrans"].Value = elTransportista.RUCTransp;
                cmd.Parameters["@RazonSocial"].Value = elTransportista.RazonSoacialTransp;
                cmd.Parameters["@Direccion"].Value = elTransportista.DirecTransp;
                cmd.Parameters["@Telefono"].Value = elTransportista.TelefTransp;
                cmd.Parameters["@Email"].Value = elTransportista.EmailTransp;
                cmd.Parameters["@RucNuevo"].Value = Ruc;
                cmd.ExecuteNonQuery();
            }
            catch (SqlException ex) { throw new Exception(ex.Message); }
            catch (Exception e) { throw new Exception(e.Message); }
            finally
            {
                if (cnn.State == ConnectionState.Open)
                {
                    cnn.Close();
                }
            }
            return true;
        }
        public DataTable ObtenerListaTransportista() {
            DataTable lista = null;
            try
            {
                cnn = (SqlConnection)ConexionSQL.getInstancia().getConexion();
                cnn.Open();
                SqlDataAdapter da = new SqlDataAdapter("ObtenerListaTransportista", cnn);
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                lista = new DataTable();
                da.Fill(lista);
            }
            catch (SqlException ex) { throw new Exception(ex.Message); }
            catch (Exception e) { throw new Exception(e.Message); }
            finally
            {
                if (cnn.State == ConnectionState.Open)
                {
                    cnn.Close();
                }
            }
            return lista;
        }
        public List<Transportista> ObtenerRUCTransportista(Int32 IDTrans)
        {
            List<Transportista> lista;
            try
            {
                cnn = (SqlConnection)ConexionSQL.getInstancia().getConexion();
                cnn.Open();
                SqlCommand cmd = new SqlCommand("ObtenerRUCTransportista", cnn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@TransportistaID", SqlDbType.Int);
                cmd.Parameters["@TransportistaID"].Value = IDTrans;
                lista = new List<Transportista>();
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Transportista elTransportista = new Transportista();
                    elTransportista.RUCTransp = dr["RUCTrans"].ToString();
                    lista.Add(elTransportista);
                }
                return lista;
            }
            catch (SqlException e) { throw new Exception(e.Message); }
            catch (Exception ex) { throw new Exception(ex.Message); }
            finally
            {
                if (cnn.State == ConnectionState.Open)
                {
                    cnn.Close();
                }
            }
        }
    }
}
